package com.bingo.mpa.portal;

import java.io.File;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.express.portal.ActionContext;
import org.express.portal.HttpUtils;
import org.express.portal.Mapping;
import org.express.portal.MultipartHttpServletRequest;
import org.express.portal.PathUtil;
import org.express.portal.Utils;
import org.express.portal.persist.QueryHelper;
import org.express.portal.renderer.BinaryRenderer;
import org.express.portal.renderer.RedirectRenderer;
import org.express.portal.renderer.Renderer;
import org.express.portal.renderer.TemplateRenderer;
import org.express.portal.renderer.TextRenderer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.bingo.mpa.dao.UserDao;
import com.bingo.mpa.pojo.Agent;
import com.bingo.mpa.pojo.Log;
import com.bingo.mpa.pojo.Phone;
import com.bingo.mpa.pojo.Store;
import com.bingo.mpa.service.AgentService;
import com.bingo.mpa.service.LogService;
import com.bingo.mpa.service.PhoneService;
import com.bingo.mpa.util.Common;
import com.bingo.mpa.util.Tools;
import com.bingo.mpa.vo.Msg;
import com.bingo.mpa.vo.Pack;
import com.bingo.mpa.vo.PhoneObject;

public class Admin
{
	private final Logger log = LoggerFactory.getLogger(getClass());
	
	/**
	 * 代理商管理
	 * @return
	 */
	@Mapping("/agent")
	public Renderer agent()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			byte role = user.getRole();
			switch(role)
			{
				case 2:
				case 3:
				case 4:
					return new RedirectRenderer("/login");
			}
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			StringBuffer sqll = new StringBuffer("SELECT COUNT(AID) FROM P_AGENT WHERE 1=1");
			if(keyword != null && keyword.length()>0)
			{
				sqll.append(" AND (NAME LIKE '%"+keyword+"%'");
				sqll.append(" OR FULLNAME LIKE '%"+keyword+"%')");
			}
			List<Object> ll = new ArrayList<Object>();
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			long count = QueryHelper.stat(sqll.toString(), param);
			model.put("keyword", keyword);
			model.put("count", count);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/agent.html","model",model);
	}
	
	@Mapping("/agent/page")
	public Renderer agent_page()
	{
		StringBuilder sb = new StringBuilder();
		try
		{
			int page = HttpUtils.getIntParameter("page",1);
			int pageSize = HttpUtils.getIntParameter("pageSize", 9);
			
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT * FROM P_AGENT WHERE 1=1");
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (NAME LIKE '%"+keyword+"%'");
				sql.append(" OR FULLNAME LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY AID");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.pojo.Agent> list = QueryHelper.query_slice(com.bingo.mpa.pojo.Agent.class, sql.toString(), page, pageSize,param);
			
			for(com.bingo.mpa.pojo.Agent obj :list)
			{
				sb.append("<tr><td width=\"120\">");
				sb.append(obj.getName());
				sb.append("</td><td>");
				sb.append(obj.getContact());
				sb.append("</td><td>");
				sb.append(obj.getContact_mobile());
				sb.append("</td><td>");
				sb.append(obj.getCom_address());
				sb.append("</td><td width=\"60\"><a href=\"#\" onclick=\"detail("+obj.getAid()+");\">");
				sb.append("增加管理员");
				sb.append("</td></tr>");
			}
			if(list.size()<9)
			{
				for(int i = 0;i<9-list.size();i++)
				{
					sb.append("<tr><td width=\"120\">");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			log.info(page+","+pageSize);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(sb.toString());
	}
	
	@Mapping("/agent/import")
	public Renderer agent_import()
	{
		String msg = "导入成功";
		com.bingo.mpa.pojo.User user = null;
		String clientIp = ActionContext.getActionContext().getHttpServletRequest().getRemoteAddr();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			
			HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest();
			InputStream input = null;
			if (request instanceof MultipartHttpServletRequest)
			{
				MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
				input = multipartRequest.getFileInputStream("fileField");
				
				Workbook workbook = Workbook.getWorkbook(input);
				Sheet sheet = workbook.getSheet("代理商");
				int row = sheet.getRows();
				Agent agent = null;
				long aid = -1;
				sql = "SELECT * FROM P_AGENT WHERE NAME=?";
				String updatesql = "UPDATE P_AGENT SET FULLNAME=?,LEGAL_NAME=?,LEGAL_TEL=?,LEGAL_MOBILE=?,LEGAL_EMAIL=?,REGIST_ADDRESS=?,COM_ADDRESS=?,CONTACT=?,CONTACT_TEL=?,CONTACT_MOBILE=?,CONTACT_EMAIL=? WHERE AID=?";
				if(row == 2)//一个文件一个代理商，所以只有一条代理商信息
				{
					String name = trim(sheet.getCell(0,1).getContents());
					if(name != null && name.length()>0)
					{
						String fullname = trim(sheet.getCell(1,1).getContents());
						String legal_name = trim(sheet.getCell(2,1).getContents());
						String legal_tel = trim(sheet.getCell(3,1).getContents());
						String legal_mobile = trim(sheet.getCell(4,1).getContents());
						String legal_email = trim(sheet.getCell(5,1).getContents());
						String regist_address = trim(sheet.getCell(6,1).getContents());
						String com_address = trim(sheet.getCell(7,1).getContents());
						String contact = trim(sheet.getCell(8,1).getContents());
						String contact_tel = trim(sheet.getCell(9,1).getContents());
						String contact_mobile = trim(sheet.getCell(10,1).getContents());
						String contact_email = trim(sheet.getCell(11,1).getContents());
						
						agent = QueryHelper.get(Agent.class, sql, new Object[]{name});
						if(agent == null)
						{
							agent = new Agent();
							agent.setName(name);
							agent.setFullname(fullname);
							agent.setLegal_name(legal_name);
							agent.setLegal_tel(legal_tel);
							agent.setLegal_mobile(legal_mobile);
							agent.setLegal_email(legal_email);
							agent.setRegist_address(regist_address);
							agent.setCom_address(com_address);
							agent.setContact(contact);
							agent.setContact_tel(contact_tel);
							agent.setContact_mobile(contact_mobile);
							agent.setContact_email(contact_email);
							aid = agent.save();
						}
						else
						{
							aid = agent.getAid();
							QueryHelper.update(updatesql, new Object[]{fullname,legal_name,legal_tel,legal_mobile,legal_email,regist_address,com_address,contact,contact_tel,contact_mobile,contact_email,aid});
						}
					}
					if(aid > 0)//处理门店
					{
						sheet = workbook.getSheet("门店");
						row = sheet.getRows();
						Store store = null;
						for(int i=1; i<row; i++)
						{
							name = trim(sheet.getCell(0,i).getContents());
							if(name == null || name.length()==0)continue;
							String fullname = trim(sheet.getCell(1,i).getContents());
							String address = trim(sheet.getCell(2,i).getContents());
							String manager = trim(sheet.getCell(3,i).getContents());
							String phone = trim(sheet.getCell(4,i).getContents());
							String mobile = trim(sheet.getCell(5,i).getContents());
							
							store = new Store();
							store.setAid(aid);
							store.setName(name);
							store.setFullname(fullname);
							store.setAddress(address);
							store.setManager(manager);
							store.setPhone(phone);
							store.setMobile(mobile);
							AgentService.getInstance().add(store);
						}
					}
				}
				Log log = new Log(clientIp,(byte)1,(byte)1,"工号为"+user.getAccount()+"的用户导入代理商，成功！");
				LogService.getInstance().write(log);
			}
		}
		catch(Exception e)
		{
			msg = "excel格式错误导入失败！";
			log.error("",e);
			Log log = new Log(clientIp,(byte)1,(byte)1,"工号为"+user!=null?user.getAccount():"会话过期"+"的用户导入代理商，失败！");
			LogService.getInstance().write(log);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
	}
	
	/**
	 * 打开新增代理商管理员页面
	 * @return
	 */
	@Mapping("/agent/manager/add")
	public Renderer agent_manager_add()
	{
		try
		{
			long aid = HttpUtils.getLongParameter("aid", -1);
			if(aid > 0)
			{
				String sql = "SELECT COUNT(*) FROM P_USER WHERE ROLE=4 AND AID=?";
				long count = QueryHelper.stat(sql, new Object[]{aid});
				if(count == 0)
				{
					sql = "SELECT * FROM P_AGENT WHERE AID = ?";
					com.bingo.mpa.pojo.Agent agent = QueryHelper.get(com.bingo.mpa.pojo.Agent.class, sql, new Object[]{aid});
					if(agent != null)
					{
						com.bingo.mpa.pojo.User user = new com.bingo.mpa.pojo.User();
						user.setName(agent.getName());
						
						sql = "SELECT * FROM P_SEQ WHERE TYPE=4";
						com.bingo.mpa.pojo.UserSeq us = QueryHelper.get(com.bingo.mpa.pojo.UserSeq.class, sql, new Object[]{});
						if(us != null)
						{
							user.setAccount(String.valueOf(us.getSeq()));
							return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(user),"utf-8");
						}
					}
				}
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 编辑用户信息
	 * @return
	 */
	@Mapping("/agent/manager/post")
	public Renderer agent_manager_post()
	{
		Msg msg = new Msg();
		try
		{
			long aid = HttpUtils.getLongParameter("aid", -1);
			String name = HttpUtils.getStringParameter("name");
			String msn = HttpUtils.getStringParameter("msn");
			int qq = HttpUtils.getIntParameter("qq", 0); 
			String mobile = HttpUtils.getStringParameter("mobile");
			String email = HttpUtils.getStringParameter("email");
			String province = HttpUtils.getStringParameter("province");
			String city = HttpUtils.getStringParameter("city");
			String address = HttpUtils.getStringParameter("address");
			String postcode = HttpUtils.getStringParameter("postcode");
			
			log.info("name="+name+",mobile="+mobile);
			//分配帐号
			long account = UserDao.getSeq(4);
			if(aid > 0 && account > 12000)
			{
				com.bingo.mpa.pojo.User usr = new com.bingo.mpa.pojo.User();
				usr.setAid(aid);
				usr.setSid(0L);
				usr.setAccount(String.valueOf(account));
				usr.setName(name);
				usr.setMsn(msn);
				usr.setQq(qq);
				usr.setMobile(mobile);
				usr.setEmail(email);
				usr.setProvince(province);
				usr.setCity(city);
				usr.setAddress(address);
				usr.setPostcode(postcode);
				usr.setGender((byte)0);
				usr.setRole((byte)4);
				usr.setStatus((byte)2);
				usr.save();
				msg.setMsg("true");
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	private String trim(String src)
	{
		if( src == null || src.isEmpty() )
		{
			return "";
		}
		src = src.trim();
		if( src.isEmpty() )
		{
			return "";
		}
		char c = src.charAt(src.length()-1);
		if( c == 0x3f || c == 160 )
		{
			src = src.substring(0, src.length() - 1);
		}
		return src;
	}
	
	/**
	 * 门店管理
	 * @return
	 */
	@Mapping("/store")
	public Renderer store()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			byte role = user.getRole();
			switch(role)
			{
				case 2:
				case 3:
				case 4:
					return new RedirectRenderer("/login");
			}
			
			long aid = HttpUtils.getLongParameter("aid", -1);
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			StringBuffer sqll = new StringBuffer("SELECT COUNT(SID) FROM P_STORE WHERE 1=1");
			if(aid > 0)
			{
				sqll.append(" AND AID = "+aid);
			}
			if(keyword != null && keyword.length()>0)
			{
				sqll.append(" AND (NAME LIKE '%"+keyword+"%'");
				sqll.append(" OR FULLNAME LIKE '%"+keyword+"%')");
			}
			List<Object> ll = new ArrayList<Object>();
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			long count = QueryHelper.stat(sqll.toString(), param);
			model.put("keyword", keyword);
			model.put("count", count);
			
			sql = "SELECT * FROM P_AGENT";
			List<com.bingo.mpa.pojo.Agent> list = QueryHelper.query_cache(com.bingo.mpa.pojo.Agent.class, "stat_agents", "stat_agents", sql, new Object[]{});
			model.put("list", list);
			model.put("aid", aid);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/store.html","model",model);
	}
	
	@Mapping("/store/page")
	public Renderer store_page()
	{
		StringBuilder sb = new StringBuilder();
		try
		{
			int page = HttpUtils.getIntParameter("page",1);
			int pageSize = HttpUtils.getIntParameter("pageSize", 9);
			
			long aid = HttpUtils.getLongParameter("aid", -1);
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT * FROM P_STORE WHERE 1=1");
			if(aid > 0)
			{
				sql.append(" AND AID = "+aid);
			}
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (NAME LIKE '%"+keyword+"%'");
				sql.append(" OR FULLNAME LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY SID");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.pojo.Store> list = QueryHelper.query_slice(com.bingo.mpa.pojo.Store.class, sql.toString(), page, pageSize,param);
			
			for(com.bingo.mpa.pojo.Store obj :list)
			{
				sb.append("<tr><td width=\"120\">");
				sb.append(obj.getName());
				sb.append("</td><td>");
				sb.append(obj.getManager());
				sb.append("</td><td>");
				sb.append(obj.getMobile());
				sb.append("</td><td>");
				sb.append(obj.getAddress());
				//sb.append("</td><td width=\"60\"><a href=\"#\" onclick=\"detail("+obj.getSid()+");\">");
				//sb.append("操作");
				sb.append("</td></tr>");
			}
			if(list.size()<9)
			{
				for(int i = 0;i<9-list.size();i++)
				{
					sb.append("<tr><td width=\"120\">");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					//sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			log.info(page+","+pageSize);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(sb.toString());
	}
	
	/**
	 * 用户管理
	 * @return
	 */
	@Mapping("/user")
	public Renderer user()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			byte role = user.getRole();
			switch(role)
			{
				case 2:
				case 3:
				case 4:
					return new RedirectRenderer("/login");
			}
			
			long aid = HttpUtils.getLongParameter("aid", -1);
			int r = HttpUtils.getIntParameter("role", -1);
			int status = HttpUtils.getIntParameter("status", -1);
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			StringBuffer sqll = new StringBuffer("SELECT COUNT(UID) FROM P_USER WHERE UID > 4");
			if(aid > 0)
			{
				sqll.append(" AND AID = "+aid);
			}
			if(r > 0)
			{
				sqll.append(" AND ROLE = "+r);
			}
			if(status > 0)
			{
				sqll.append(" AND STATUS = "+status);
			}
			if(keyword != null && keyword.length()>0)
			{
				sqll.append(" AND (ACCOUNT LIKE '%"+keyword+"%'");
				sqll.append(" OR NAME LIKE '%"+keyword+"%')");
			}
			List<Object> ll = new ArrayList<Object>();
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			long count = QueryHelper.stat(sqll.toString(), param);
			model.put("keyword", keyword);
			model.put("count", count);
			
			sql = "SELECT * FROM P_AGENT";
			List<com.bingo.mpa.pojo.Agent> list = QueryHelper.query_cache(com.bingo.mpa.pojo.Agent.class, "stat_agents", "stat_agents", sql, new Object[]{});
			model.put("list", list);
			model.put("aid", aid);
			model.put("role", r);
			model.put("status", status);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/user.html","model",model);
	}
	
	@Mapping("/user/page")
	public Renderer user_page()
	{
		StringBuilder sb = new StringBuilder();
		try
		{
			int page = HttpUtils.getIntParameter("page",1);
			int pageSize = HttpUtils.getIntParameter("pageSize", 9);
			
			long aid = HttpUtils.getLongParameter("aid", -1);
			int r = HttpUtils.getIntParameter("role", -1);
			int status = HttpUtils.getIntParameter("status", -1);
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT * FROM P_USER WHERE UID > 4");
			if(aid > 0)
			{
				sql.append(" AND AID = "+aid);
			}
			if(r > 0)
			{
				sql.append(" AND ROLE = "+r);
			}
			if(status > 0)
			{
				sql.append(" AND STATUS = "+status);
			}
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (ACCOUNT LIKE '%"+keyword+"%'");
				sql.append(" OR NAME LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY UID DESC");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.pojo.User> list = QueryHelper.query_slice(com.bingo.mpa.pojo.User.class, sql.toString(), page, pageSize,param);
			
			for(com.bingo.mpa.pojo.User obj :list)
			{
				sb.append("<tr><td width=\"80\">");
				sb.append(obj.getAccount());
				sb.append("</td><td>");
				sb.append(obj.getName());
				sb.append("</td><td>");
				sb.append(obj.getMobile());
				sb.append("</td><td>");
				sb.append(obj.getRole()==1?"管理员":obj.getRole()==2?"开卡员":obj.getRole()==3?"营业员":obj.getRole()==4?"代理商":"未知");
				sb.append("</td><td>");
				sb.append(obj.getStatus()==1?"待审核":obj.getStatus()==2?"正常":obj.getStatus()==3?"锁定":obj.getStatus()==4?"删除":obj.getStatus()==5?"未通过":"未知");
				sb.append("</td><td>");
				sb.append(obj.getAddress());
				sb.append("</td><td width=\"80\"><a href=\"#\" onclick=\"detail("+obj.getUid()+");\">编辑</a>");
				sb.append("&nbsp;|&nbsp;<a href=\"#\" onclick=\"pwd("+obj.getUid()+",'"+obj.getAccount()+"');\">密码</a>");
				sb.append("&nbsp;|&nbsp;<a href=\"#\" onclick=\"del("+obj.getUid()+");\">删除</a>");
				sb.append("</td></tr>");
			}
			if(list.size()<9)
			{
				for(int i = 0;i<9-list.size();i++)
				{
					sb.append("<tr><td width=\"80\">");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			log.info(page+","+pageSize);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(sb.toString());
	}
	
	/**
	 * 用户详情
	 * @return
	 */
	@Mapping("/user/detail")
	public Renderer user_detail()
	{
		try
		{
			long uid = HttpUtils.getLongParameter("uid", -1);
			if(uid > 0)
			{
				String sql = "SELECT * FROM P_USER WHERE UID=?";
				com.bingo.mpa.pojo.User user = QueryHelper.get(com.bingo.mpa.pojo.User.class, sql, new Object[]{uid});
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(user),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 编辑用户信息
	 * @return
	 */
	@Mapping("/admin/usr/mod")
	public Renderer mod_usr()
	{
		Msg msg = new Msg();
		try
		{
			long usrid = HttpUtils.getLongParameter("usrid", -1);
			String msn = HttpUtils.getStringParameter("msn");
			int qq = HttpUtils.getIntParameter("qq", 0); 
			String mobile = HttpUtils.getStringParameter("mobile");
			String email = HttpUtils.getStringParameter("email");
			String province = HttpUtils.getStringParameter("province");
			String city = HttpUtils.getStringParameter("city");
			String address = HttpUtils.getStringParameter("address");
			String postcode = HttpUtils.getStringParameter("postcode");
			
			log.info("uid="+usrid+",mobile="+mobile);
			if(usrid > 0)
			{
				String sql = "UPDATE P_USER SET MSN=?,QQ=?,MOBILE = ?,EMAIL=?,PROVINCE=?,CITY=?,ADDRESS=?,POSTCODE=? WHERE UID=?";
				QueryHelper.update(sql, new Object[]{msn,qq,mobile,email,province,city,address,postcode,usrid});
				msg.setMsg("true");
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 修改用户密码
	 * 如果用户被注销，重新激活
	 * @return
	 */
	@Mapping("/admin/usr/pwd")
	public Renderer mod_pwd()
	{
		Msg msg = new Msg();
		try
		{
			long uid = HttpUtils.getLongParameter("uid", -1);
			String pwd = HttpUtils.getStringParameter("newpwd");
			log.info("uid="+uid+",pwd="+pwd);
			if(uid > 0)
			{
				pwd = Utils.getMd5Sum(pwd);
				String sql = "UPDATE P_USER SET STATUS=2,PWD = ? WHERE UID=?";
				QueryHelper.update(sql, new Object[]{pwd,uid});
				msg.setMsg("true");
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 删除用户
	 * @return
	 */
	@Mapping("/admin/usr/del")
	public Renderer usr_del()
	{
		Msg msg = new Msg();
		try
		{
			long uid = HttpUtils.getLongParameter("uid", -1);
			log.info("uid="+uid);
			if(uid > 0)
			{
				String sql = "UPDATE P_USER SET STATUS = 4 WHERE UID=?";
				QueryHelper.update(sql, new Object[]{uid});
				msg.setMsg("true");
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	@Mapping("/admin/phone")
	public Renderer phone()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			byte role = user.getRole();
			switch(role)
			{
				case 2:
				case 3:
				case 4:
					return new RedirectRenderer("/login");
			}
			int carrier = HttpUtils.getIntParameter("carrier", -1);
			int brand = HttpUtils.getIntParameter("brand", -1);
			int _status = HttpUtils.getIntParameter("_status", -1);
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			StringBuffer sqll = new StringBuffer("SELECT COUNT(PID) FROM P_PHONE WHERE 1=1");
			List<Object> ll = new ArrayList<Object>();
			if(carrier > 0)
			{
				sqll.append(" AND CARRIER=?");
				ll.add(carrier);
			}
			if(brand >0)
			{
				sqll.append(" AND BRAND=?");
				ll.add(brand);
			}
			if(_status > 0)
			{
				sqll.append(" AND STATUS=?");
				ll.add(_status);
			}
			if(keyword != null && keyword.length()>0)
			{
				sqll.append(" AND (PHONE LIKE '%"+keyword+"%')");
			}
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			long count = QueryHelper.stat(sqll.toString(), param);
			model.put("keyword", keyword);
			model.put("carrier", carrier);
			model.put("brand", brand);
			model.put("_status", _status);
			model.put("count", count);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/phone_admin.html","model",model);
	}
	
	@Mapping("/admin/phone/page")
	public Renderer phone_page()
	{
		StringBuilder sb = new StringBuilder();
		try
		{
			int page = HttpUtils.getIntParameter("page",1);
			int pageSize = HttpUtils.getIntParameter("pageSize", 9);
			
			int carrier = HttpUtils.getIntParameter("carrier", -1);
			int brand = HttpUtils.getIntParameter("brand", -1);
			int _status = HttpUtils.getIntParameter("_status", -1);
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT * FROM P_PHONE WHERE 1=1");
			if(carrier > 0)
			{
				sql.append(" AND CARRIER=?");
				ll.add(carrier);
			}
			if(brand >0)
			{
				sql.append(" AND BRAND=?");
				ll.add(brand);
			}
			if(_status > 0)
			{
				sql.append(" AND STATUS=?");
				ll.add(_status);
			}
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (PHONE LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY PID");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.pojo.Phone> objs = QueryHelper.query_slice(com.bingo.mpa.pojo.Phone.class, sql.toString(), page, pageSize,param);
			
			NumberFormat  currentFormat=NumberFormat.getCurrencyInstance(Locale.CHINA);
			for(com.bingo.mpa.pojo.Phone obj :objs)
			{
				sb.append("<tr><td class=\"num\">");
				sb.append(obj.getPhone().substring(0, 7));
				sb.append("<span class=\"orange\">&nbsp;"+obj.getPhone().substring(7)+"</span>");
				sb.append("</td><td>");
				sb.append(obj.getCarrier()==1?"中国移动":obj.getCarrier()==2?"中国联通":obj.getCarrier()==3?"中国电信":"");
				sb.append("</td><td>");
				sb.append(currentFormat.format(obj.getRetail()/100.0));
				sb.append("</td><td>");
				sb.append(currentFormat.format(obj.getDiscount()/100.0));
				sb.append("</td><td>");
				sb.append(obj.getStatus()==1?"未开卡":obj.getStatus()==2?"已锁定":obj.getStatus()==3?"已开卡":obj.getStatus()==4?"已下架":"");
				sb.append("</td><td width=\"60\"><a href=\"#\" onclick=\"detail("+obj.getPid()+","+obj.getStatus()+");\">");
				sb.append("操作</a>");
				sb.append("</td></tr>");
			}
			if(objs.size()<9)
			{
				for(int i = 0;i<9-objs.size();i++)
				{
					sb.append("<tr><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			log.info(page+","+pageSize);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(sb.toString());
	}
	
	@Mapping("/admin/phone/import")
	public Renderer phone_import()
	{
		String msg = "导入成功";
		try
		{
			HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest();
			InputStream input = null;
			if (request instanceof MultipartHttpServletRequest)
			{
				MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
				input = multipartRequest.getFileInputStream("fileField");
				
				Workbook workbook = Workbook.getWorkbook(input);
				Sheet sheet = workbook.getSheet(0);
				int row = sheet.getRows();
				Phone obj = null;

				for(int i=1; i<row; i++)
				{
					String phone = trim(sheet.getCell(0,i).getContents());
					String _carrier = trim(sheet.getCell(1,i).getContents());
					String _brand = trim(sheet.getCell(2,i).getContents());
					String _retail = trim(sheet.getCell(3,i).getContents());
					String _discount = trim(sheet.getCell(4,i).getContents());
					String province = trim(sheet.getCell(5,i).getContents());
					String city = trim(sheet.getCell(6,i).getContents());
					String _agent = trim(sheet.getCell(7,i).getContents());
					String _card_type = null;
					if(sheet.getColumns()>8)
					{
						_card_type = trim(sheet.getCell(8,i).getContents());
					}

					if(phone != null && phone.length()>0)
					{
						obj = new Phone();
						obj.setPhone(phone);
						if(_carrier != null)
						{
							if(_carrier.equals("中国移动") || _carrier.indexOf("移动")!=-1)
							{
								obj.setCarrier((byte)1);
							}
							else if(_carrier.equals("中国联通") || _carrier.indexOf("联通") != -1)
							{
								obj.setCarrier((byte)2);
							}
							else if(_carrier.equals("中国电信") || _carrier.indexOf("电信") != -1)
							{
								obj.setCarrier((byte)3);
							}
						}
						if(_brand != null)
						{
							if(_brand.equals("全球通"))
							{
								obj.setBrand((byte)10);
							}
							else if(_brand.equals("动感地带"))
							{
								obj.setBrand((byte)11);
							}
							else if(_brand.equals("动感地带网聊卡"))
							{
								obj.setBrand((byte)41);
							}
							else if(_brand.equals("动感地带3G流量卡"))
							{
								obj.setBrand((byte)42);
							}
							else if(_brand.equals("神州行"))
							{
								obj.setBrand((byte)12);
							}
							else if(_brand.equals("神州行3G流量卡"))
							{
								obj.setBrand((byte)71);
							}
							else if(_brand.equals("神州行轻松卡"))
							{
								obj.setBrand((byte)72);
							}
							else if(_brand.equalsIgnoreCase("3G"))
							{
								obj.setBrand((byte)20);
							}
							else if(_brand.equals("如意通"))
							{
								obj.setBrand((byte)21);
							}
							else if(_brand.equals("特区行"))
							{
								obj.setBrand((byte)22);
							}
							else if(_brand.equals("新势力"))
							{
								obj.setBrand((byte)23);
							}
							else if(_brand.equals("up新势力先锋卡"))
							{
								obj.setBrand((byte)81);
							}
							else if(_brand.equals("up新势力流量卡"))
							{
								obj.setBrand((byte)82);
							}
						}
						if(_card_type != null && _card_type.length() > 0)
						{
							if(_card_type.equals("芯1") || _card_type.equals("芯一"))
							{
								obj.setCard_type((byte)1);
							}
							else if(_card_type.equals("芯2") || _card_type.equals("芯二"))
							{
								obj.setCard_type((byte)2);
							}
						}
						if(_retail != null && _retail.length() > 0)
						{
							obj.setRetail(Integer.parseInt(_retail)*100);
						}
						if(_discount != null && _discount.length() > 0)
						{
							obj.setDiscount(Integer.parseInt(_discount)*100);
						}
						obj.setProvince(province);
						obj.setCity(city);
						PhoneObject<Phone,String> po = new PhoneObject<Phone,String>(obj,_agent);
						PhoneService.getInstance().add(po);
					}
				}
			}
		}
		catch(Exception e)
		{
			msg = "excel格式错误导入失败！";
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
	}
	
	/**
	 * 号码详情
	 * @return
	 */
	@Mapping("/admin/phone/detail")
	public Renderer phone_detail()
	{
		try
		{
			long pid = HttpUtils.getLongParameter("pid", -1);
			if(pid > 0)
			{
				String sql = "SELECT * FROM P_PHONE WHERE PID=?";
				com.bingo.mpa.pojo.Phone obj = QueryHelper.get(com.bingo.mpa.pojo.Phone.class, sql, new Object[]{pid});
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(obj),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 修改号码信息
	 * @return
	 */
	@Mapping("/admin/phone/mod")
	public Renderer mod_phone()
	{
		Msg msg = new Msg();
		try
		{
			long pid = HttpUtils.getLongParameter("pid", -1);
			int retail = HttpUtils.getIntParameter("retail", 0);
			int discount = HttpUtils.getIntParameter("discount", 0);
			String province = HttpUtils.getStringParameter("province");
			String city = HttpUtils.getStringParameter("city");
			
			log.info("pid="+pid);
			if(pid > 0)
			{
				String sql = "UPDATE P_PHONE SET RETAIL=?,DISCOUNT=?,PROVINCE=?,CITY=? WHERE PID=?";
				QueryHelper.update(sql, new Object[]{retail*100,discount*100,province,city,pid});
				msg.setMsg("true");
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 号码上/下架
	 * @return
	 */
	@Mapping("/admin/phone/shelve")
	public Renderer shelve_phone()
	{
		Msg msg = new Msg();
		try
		{
			long pid = HttpUtils.getLongParameter("pid", -1);
			int status = HttpUtils.getIntParameter("status", -1);
			
			log.info("pid="+pid+",status="+status);
			if(pid > 0)
			{
				if(status ==1)status =4;
				else if(status ==4)status =1;
				String sql = "UPDATE P_PHONE SET STATUS=? WHERE PID=?";
				QueryHelper.update(sql, new Object[]{status,pid});
				msg.setMsg("true");
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	@Mapping("/admin/phone/clear")
	public Renderer phone_clear()
	{
		Msg msg = new Msg();
		try
		{
			int carrier = HttpUtils.getIntParameter("carrier", -1);
			int brand = HttpUtils.getIntParameter("brand", -1);
			if(carrier > 0 && brand > 0)
			{
				String sql = "DELETE FROM P_PHONE WHERE CARRIER="+carrier +" AND BRAND="+brand+" AND (STATUS= 1 OR STATUS = 4)";
				QueryHelper.update(sql, new Object[]{});
				msg.setMsg("true");
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("清除号码异常",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 导出号码
	 * @return
	 */
	@Mapping("/admin/phone/export")
	public Renderer phone_export()
	{
		HttpServletResponse response = ActionContext.getActionContext().getHttpServletResponse();
		WritableWorkbook wwb;
		try
		{
			int carrier = HttpUtils.getIntParameter("carrier", -1);
			int brand = HttpUtils.getIntParameter("brand", -1);
			int _status = HttpUtils.getIntParameter("_status", -1);
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT p.*,a.NAME FROM P_PHONE p LEFT JOIN P_AGENT a ON p.AID=a.AID WHERE p.PID>0");
			if(carrier > 0)
			{
				sql.append(" AND p.CARRIER=?");
				ll.add(carrier);
			}
			if(brand >0)
			{
				sql.append(" AND p.BRAND=?");
				ll.add(brand);
			}
			if(_status > 0)
			{
				sql.append(" AND p.STATUS=?");
				ll.add(_status);
			}
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (p.PHONE LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY p.PID");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.vo.PhoneVO> objs = QueryHelper.query(com.bingo.mpa.vo.PhoneVO.class, sql.toString(),param);
			
			wwb = Workbook.createWorkbook(response.getOutputStream());
			WritableSheet ws = wwb.createSheet("号码", 0);
			//列头
			ws.addCell(new Label(0, 0, "号码"));
			ws.addCell(new Label(1, 0, "运营商"));
			ws.addCell(new Label(2, 0, "品牌"));
			ws.addCell(new Label(3, 0, "零售价"));
			ws.addCell(new Label(4, 0, "折扣价"));
			ws.addCell(new Label(5, 0, "归属地（省）"));
			ws.addCell(new Label(6, 0, "归属地（市）"));
			ws.addCell(new Label(7, 0, "专属代理商"));
			ws.addCell(new Label(8, 0, "卡类型"));
			
			int i = 1;
			String carrierName = "";
			String brandName = "";
			for(com.bingo.mpa.vo.PhoneVO obj :objs)
			{
				ws.addCell(new Label(0,i,obj.getPhone()));
				switch(obj.getBrand())
				{
					case 10:
						carrierName = "中国移动";
						brandName = "全球通";
						break;
					case 11:
						carrierName = "中国移动";
						brandName = "动感地带";
						break;
					case 41:
						carrierName = "中国移动";
						brandName = "动感地带网聊卡";
						break;
					case 42:
						carrierName = "中国移动";
						brandName = "动感地带3G流量卡";
						break;
					case 12:
						carrierName = "中国移动";
						brandName = "神州行";
						break;
					case 71:
						carrierName = "中国移动";
						brandName = "神州行3G流量卡";
						break;
					case 72:
						carrierName = "中国移动";
						brandName = "神州行轻松卡";
						break;
					case 20:
						carrierName = "中国联通";
						brandName = "3G";
						break;
					case 21:
						carrierName = "中国联通";
						brandName = "如意通";
						break;
					case 22:
						carrierName = "中国联通";
						brandName = "特区行";
						break;
					case 23:
						carrierName = "中国联通";
						brandName = "新势力";
						break;
					case 81:
						carrierName = "中国联通";
						brandName = "up新势力先锋卡";
						break;
					case 82:
						carrierName = "中国联通";
						brandName = "up新势力流量卡";
						break;
					default:
						break;
				}
				ws.addCell(new Label(1,i,carrierName));
				ws.addCell(new Label(2,i,brandName));
				ws.addCell(new Label(3,i,String.valueOf(obj.getRetail()/100)));
				ws.addCell(new Label(4,i,String.valueOf(obj.getDiscount()/100)));
				ws.addCell(new Label(5,i,obj.getProvince()));
				ws.addCell(new Label(6,i,obj.getCity()));
				ws.addCell(new Label(7,i,obj.getName()));
				ws.addCell(new Label(8,i,obj.getCard_type()==1?"芯1":obj.getCard_type()==2?"芯2":"未指定"));
				i++;
			}
			
			response.reset(); // 非常重要   
            response.setContentType("application/x-msdownload");   
            response.setHeader("Content-Disposition","attachment;filename=phone.xls");   
			wwb.write();
			wwb.close();
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return null;
	}
	
	@Mapping("/admin/package")
	public Renderer _package()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			byte role = user.getRole();
			switch(role)
			{
				case 2:
				case 3:
				case 4:
					return new RedirectRenderer("/login");
			}
			
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			StringBuffer sqll = new StringBuffer("SELECT COUNT(PID) FROM P_PACKAGE WHERE 1=1");
			List<Object> ll = new ArrayList<Object>();
			if(keyword != null && keyword.length()>0)
			{
				sqll.append(" AND (NAME LIKE '%"+keyword+"%')");
			}
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			long count = QueryHelper.stat(sqll.toString(), param);
			model.put("keyword", keyword);
			model.put("count", count);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/package_admin.html","model",model);
	}
	
	@Mapping("/admin/package/page")
	public Renderer package_page()
	{
		StringBuilder sb = new StringBuilder();
		try
		{
			int page = HttpUtils.getIntParameter("page",1);
			int pageSize = HttpUtils.getIntParameter("pageSize", 9);
			
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT * FROM P_PACKAGE WHERE 1=1");
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (NAME LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY PID");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.pojo.Package> objs = QueryHelper.query_slice(com.bingo.mpa.pojo.Package.class, sql.toString(), page, pageSize,param);
			
			for(com.bingo.mpa.pojo.Package obj :objs)
			{
				sb.append("<tr><td class=\"num\">");
				sb.append(obj.getName());
				sb.append("</td><td>");
				sb.append(obj.getCarrier()==1?"中国移动":obj.getCarrier()==2?"中国联通":obj.getCarrier()==3?"中国电信":"");
				sb.append("</td><td>");
				sb.append(obj.getFee());
				sb.append("</td><td>");
				sb.append(obj.getVoice());
				sb.append("</td><td>");
				sb.append(obj.getTraffic());
				sb.append("</td><td>");
				sb.append(obj.getSms());
				sb.append("</td></tr>");
			}
			if(objs.size()<9)
			{
				for(int i = 0;i<9-objs.size();i++)
				{
					sb.append("<tr><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			log.info(page+","+pageSize);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(sb.toString());
	}
	
	@Mapping("/admin/package/import")
	public Renderer package_import()
	{
		String msg = "导入成功";
		try
		{  
			HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest();
			InputStream input = null;
			if (request instanceof MultipartHttpServletRequest)
			{
				MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
				input = multipartRequest.getFileInputStream("fileField");
				
				Workbook workbook = Workbook.getWorkbook(input);
				Sheet sheet = workbook.getSheet(0);
				int row = sheet.getRows();
				com.bingo.mpa.pojo.Package obj = null;
				byte carrier = 2;
				String sql = "SELECT * FROM P_PACKAGE WHERE NAME=? AND CARRIER=?";
				String updatesql = "UPDATE P_PACKAGE SET FEE=?,TYPE=?,VOICE=?,TRAFFIC=?,MCOUNT=?,TCOUNT=?,VIDEO=?,SMS=?,EX_VOICE=?,SCOPE=?,EX_VIDEO=?,EX_TRAFFIC=?,VAS=?,OTHER=?,CONTENT=?,CONTENT_DETAIL=?,PRESTORE=?,PRE_CONTENT=?,PRE_CONT_DETAIL=? WHERE PID=? AND CARRIER=?";
				for(int i=1; i<row; i++)
				{
					String _carrier = trim(sheet.getCell(0,i).getContents());
					if(_carrier != null && _carrier.length() > 0)
					{
						if(_carrier.equals("中国移动") || _carrier.indexOf("移动")!=-1)
						{
							carrier = 1;
						}
						if(_carrier.equals("中国联通") || _carrier.indexOf("联通")!=-1)
						{
							carrier = 2;
						}
						if(_carrier.equals("中国电信") || _carrier.indexOf("电信")!=-1)
						{
							carrier = 3;
						}
					}
					String name = trim(sheet.getCell(1,i).getContents());
					String fee = trim(sheet.getCell(2,i).getContents());
					String type = trim(sheet.getCell(3,i).getContents());
					String voice = trim(sheet.getCell(4,i).getContents());
					String traffic = trim(sheet.getCell(5,i).getContents());
					String mcount = trim(sheet.getCell(6,i).getContents());
					String tcount = trim(sheet.getCell(7,i).getContents());
					String video = trim(sheet.getCell(8,i).getContents());
					String sms = trim(sheet.getCell(9,i).getContents());
					String ex_voice = trim(sheet.getCell(10,i).getContents());
					String scope = trim(sheet.getCell(11,i).getContents());
					String ex_video = trim(sheet.getCell(12,i).getContents());
					String ex_traffic = trim(sheet.getCell(13,i).getContents());
					String vas = trim(sheet.getCell(14,i).getContents());
					String other = trim(sheet.getCell(15,i).getContents());
					String content = trim(sheet.getCell(16,i).getContents());
					String content_detail = trim(sheet.getCell(17,i).getContents());
					String _prestore = trim(sheet.getCell(18,i).getContents());
					String pre_content = trim(sheet.getCell(19,i).getContents());
					String pre_cont_detail = trim(sheet.getCell(20,i).getContents());
					
					int prestore = 0;
					if(_prestore != null)prestore=Integer.parseInt(_prestore);
					obj = QueryHelper.get(com.bingo.mpa.pojo.Package.class, sql, new Object[]{name,carrier});
					if(obj == null)
					{
						obj = new com.bingo.mpa.pojo.Package();
						obj.setCarrier(carrier);
						obj.setName(name);
						obj.setFee(fee);
						obj.setType(type);
						obj.setVoice(voice);
						obj.setTraffic(traffic);
						obj.setMcount(mcount);
						obj.setTcount(tcount);
						obj.setVideo(video);
						obj.setSms(sms);
						obj.setEx_voice(ex_voice);
						obj.setScope(scope);
						obj.setEx_video(ex_video);
						obj.setEx_traffic(ex_traffic);
						obj.setVas(vas);
						obj.setOther(other);
						obj.setContent(content);
						obj.setContent_detail(content_detail);
						obj.setPrestore(prestore);
						obj.setPre_content(pre_content);
						obj.setPre_cont_detail(pre_cont_detail);
						obj.save();
					}
					else
					{
						QueryHelper.update(updatesql, new Object[]{fee,type,voice,traffic,mcount,tcount,video,sms,ex_voice,scope,ex_video,ex_traffic,vas,other,content,content_detail,prestore,pre_content,pre_cont_detail,obj.getPid(),carrier});
					}
				}
			}
		}
		catch(Exception e)
		{
			msg = "excel格式错误导入失败！";
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
	}
	
	@Mapping("/admin/order")
	public Renderer _order()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			byte role = user.getRole();
			switch(role)
			{
				case 2:
				case 3:
				case 4:
					return new RedirectRenderer("/login");
			}
			long aid = HttpUtils.getLongParameter("aid", -1);
			int status = HttpUtils.getIntParameter("status", -1);
			String dateType = HttpUtils.getStringParameter("dateType");
			String st = HttpUtils.getStringParameter("st");
			String et = HttpUtils.getStringParameter("et");
			if(dateType == null)
			{
				dateType = "today";
				st = Common.getMetaDate(dateType).getSt();
				et = Common.getMetaDate(dateType).getEt();
			}
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			StringBuffer sqll = new StringBuffer("SELECT COUNT(o.OID) FROM P_STORE s,P_ORDER o WHERE s.SID=o.SID");
			List<Object> ll = new ArrayList<Object>();
			if(aid > 0)
			{
				sqll.append(" AND o.AID="+aid);
			}
			if(status >0)
			{
				sqll.append(" AND o.STATUS="+status);
			}
			if(st != null && st.length() > 0)
			{
				sqll.append(" AND o.ORDER_TIME >=?");
				java.util.Date _st = org.apache.commons.lang.time.DateUtils.parseDate(st, new String[]{"yyyy-MM-dd"});
				ll.add(_st);
			}
			if(et != null && et.length() > 0)
			{
				sqll.append(" AND o.ORDER_TIME <?");
				java.util.Date _et = org.apache.commons.lang.time.DateUtils.parseDate(et, new String[]{"yyyy-MM-dd"});
				ll.add(_et);
			}
			if(keyword != null && keyword.length()>0)
			{
				sqll.append(" AND (o.PHONE LIKE '%"+keyword+"%')");
			}
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			long count = QueryHelper.stat(sqll.toString(), param);
			model.put("keyword", keyword);
			model.put("count", count);
			
			sql = "SELECT * FROM P_AGENT";
			List<com.bingo.mpa.pojo.Agent> list = QueryHelper.query_cache(com.bingo.mpa.pojo.Agent.class, "stat_agents", "stat_agents", sql, new Object[]{});
			model.put("list", list);
			model.put("aid", aid);
			model.put("status", status);
			model.put("dateType", dateType);
			model.put("st", st);
			model.put("et", et);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/order_admin.html","model",model);
	}
	
	@Mapping("/admin/order/page")
	public Renderer order_page()
	{
		StringBuilder sb = new StringBuilder();
		try
		{
			int page = HttpUtils.getIntParameter("page",1);
			int pageSize = HttpUtils.getIntParameter("pageSize", 9);
			
			long aid = HttpUtils.getLongParameter("aid", -1);
			int status = HttpUtils.getIntParameter("status", -1);
			String st = HttpUtils.getStringParameter("st");
			String et = HttpUtils.getStringParameter("et");
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT s.NAME,o.* FROM P_STORE s,P_ORDER o WHERE s.SID=o.SID");
			if(aid > 0)
			{
				sql.append(" AND o.AID="+aid);
			}
			if(status >0)
			{
				sql.append(" AND o.STATUS="+status);
			}
			if(st != null && st.length() > 0)
			{
				sql.append(" AND o.ORDER_TIME >=?");
				java.util.Date _st = org.apache.commons.lang.time.DateUtils.parseDate(st, new String[]{"yyyy-MM-dd"});
				ll.add(_st);
			}
			if(et != null && et.length() > 0)
			{
				sql.append(" AND o.ORDER_TIME <?");
				java.util.Date _et = org.apache.commons.lang.time.DateUtils.parseDate(et, new String[]{"yyyy-MM-dd"});
				ll.add(_et);
			}
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (o.PHONE LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY o.OID DESC");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.vo.OrderVO> objs = QueryHelper.query_slice(com.bingo.mpa.vo.OrderVO.class, sql.toString(), page, pageSize,param);
			
			sql = new StringBuffer("SELECT * FROM P_PACKAGE");
			List<com.bingo.mpa.pojo.Package> lp = QueryHelper.query_cache(com.bingo.mpa.pojo.Package.class, "packages", "packages", sql.toString(), new Object[]{});
			Map<Integer,com.bingo.mpa.pojo.Package> map = new HashMap<Integer,com.bingo.mpa.pojo.Package>();
			if(lp != null && lp.size() > 0)
			{
				for(com.bingo.mpa.pojo.Package obj : lp)
				{
					map.put(Integer.valueOf(obj.getPid()), obj);
				}
			}
			NumberFormat currentFormat=NumberFormat.getCurrencyInstance(Locale.CHINA);
			com.bingo.mpa.pojo.Package tmp = null;
			for(com.bingo.mpa.vo.OrderVO obj :objs)
			{
				sb.append("<tr><td width=\"110\">");
				sb.append(org.apache.commons.lang.time.DateFormatUtils.format(obj.getOrder_time(), "yyyy/MM/dd HH:mm"));
				sb.append("</td><td width=\"120\">");
				sb.append(obj.getName());
				sb.append("</td><td width=\"30\">");
				sb.append(obj.getS_account());
				sb.append("</td><td class=\"num16\" width=\"100\">");
				sb.append(obj.getPhone().substring(0, 7));
				sb.append("<span class=\"orange\">"+obj.getPhone().substring(7)+"</span>");
				sb.append("</td><td width=\"110\">");
				tmp = map.get(obj.getPackage_());
				if(tmp != null)
				{
					switch(tmp.getCarrier())
					{
						case 1:
							sb.append("<img src=\"/images/icon_mobile.png\"/>");
							break;
						case 2:
							sb.append("<img src=\"/images/icon_unicom.png\"/>");
							break;
					}
					sb.append(tmp.getName());
				}
				sb.append("</td><td width=\"40\">");
				sb.append(currentFormat.format(obj.getRetail()/100.0));
				sb.append("</td><td width=\"40\">");
				sb.append(currentFormat.format(obj.getPrestore()/100.0));
				sb.append("</td><td width=\"40\">");
				sb.append(currentFormat.format(obj.getDiscount()/100.0));
				sb.append("</td><td width=\"40\">");
				sb.append(obj.getStatus()==1?"<span style=\"color:red\">待开卡</span>":obj.getStatus()==2?"<span style=\"color:green\">已开卡</span>":obj.getStatus()==3?"<span style=\"color:blue\">已撤单</span>":obj.getStatus()==4?"<span style=\"color:red\">已驳回</span>":"未知");
				sb.append("</td><td width=\"50\">");
				sb.append(obj.getPay_status()==1?"<span style=\"color:red\">未支付</span>":obj.getPay_status()==2?"<span style=\"color:blue\">处理中</span>":obj.getPay_status()==3?"<span style=\"color:green\">已支付</span>":obj.getPay_status()==4?"<span style=\"color:red\">失败</span>":"未知");
				sb.append("</td><td>");
				sb.append(obj.getRemark()==null?"":obj.getRemark());
				String prestore = "";
				if(obj.getPrestore()>0)
				{
					prestore = "（预存"+obj.getPrestore()/100+"）";
				}
				String pack = tmp != null?tmp.getName():"无";
				sb.append("</td><td width=\"70\"><a href=\"#\" onclick=\"detail("+obj.getOid()+",'"+pack+prestore+"');\">");
				if(obj.getId_card() != null)
				{
					sb.append("操作&nbsp;|<a href=\"/admin/order/d/"+obj.getOid()+"\">&nbsp;附件</a>");
				}
				else
				{
					sb.append("操作&nbsp;|<a href=\"#\">&nbsp;XX</a>");
				}
				sb.append("</td></tr>");
			}
			if(objs.size()<9)
			{
				for(int i = 0;i<9-objs.size();i++)
				{
					sb.append("<tr><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			log.info(page+","+pageSize);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(sb.toString());
	}
	
	/**
	 * 处理订单
	 * @return
	 */
	@Mapping("/admin/order/active")
	public Renderer order_active()
	{
		Msg msg = new Msg();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			
			long oid = HttpUtils.getLongParameter("oid", -1);
			int status = HttpUtils.getIntParameter("status", 2);
			String phone = HttpUtils.getStringParameter("phone","");
			String remark = HttpUtils.getStringParameter("remark");
			log.info("oid="+oid+",status="+status+",remark="+remark);
			if(oid > 0)
			{
				sql = "UPDATE P_ORDER SET STATUS = ?,REMARK=?,P_ACCOUNT=?,PROCE_TIME=? WHERE OID=?";
				QueryHelper.update(sql, new Object[]{status,remark,user.getAccount(),new java.sql.Timestamp(Calendar.getInstance(Locale.CHINA).getTimeInMillis()),oid});
				msg.setMsg("true");
				
				//如果驳回订单,说明号码开不了,或信息填错，重新把号码置为锁定
				if(status == 4)
				{
					//TODO:
					String _update = "UPDATE P_PHONE SET STATUS=2,ACTIVE_TIME=? WHERE PHONE=?";
					if(phone.length()>0)
					QueryHelper.update(_update, new Object[]{Calendar.getInstance(Locale.CHINA).getTime(),phone});
				}
				return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(msg),"utf-8");
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString("error"),"utf-8");
	}
	
	/**
	 * 下载附件，证件扫描
	 * @param oid
	 * @return
	 */
	@Mapping("/admin/order/d/$1")
	public Renderer order_d(long oid)
	{
		BinaryRenderer renderer = new BinaryRenderer();
		HttpServletResponse response = ActionContext.getActionContext().getHttpServletResponse();
		try
		{
			log.info("oid="+oid);
			if(oid > 0)
			{
				String sql = "SELECT * FROM P_ORDER WHERE OID=?";
				com.bingo.mpa.pojo.Order obj = QueryHelper.get(com.bingo.mpa.pojo.Order.class, sql, new Object[]{oid});
				if(obj != null)
				{
					String id_card = obj.getId_card();
					if(id_card != null)
					{
						File file = PathUtil.getAppPath();
						file = new File(file,"data");
						file = new File(file,id_card);
						if(file.exists())
						{
							byte[] data = org.apache.commons.io.FileUtils.readFileToByteArray(file);
							renderer.setData(data);
							renderer.setContentType("application/x-download");
							String fn = obj.getPhone()+file.getPath().substring(file.getPath().lastIndexOf("."));
							response.addHeader("Content-Disposition","attachment;filename=" + fn);
						}
					}
				}
			}
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return renderer;
	}
	
	/**
	 * 导出订单
	 * @return
	 */
	@Mapping("/admin/order/export")
	public Renderer order_export()
	{
		HttpServletResponse response = ActionContext.getActionContext().getHttpServletResponse();
		WritableWorkbook wwb;
		try
		{
			long aid = HttpUtils.getLongParameter("aid", -1);
			int status = HttpUtils.getIntParameter("status", -1);
			String st = HttpUtils.getStringParameter("st");
			String et = HttpUtils.getStringParameter("et");
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT s.NAME,o.* FROM P_STORE s,P_ORDER o WHERE s.SID=o.SID");
			if(aid > 0)
			{
				sql.append(" AND o.AID="+aid);
			}
			if(status >0)
			{
				sql.append(" AND o.STATUS="+status);
			}
			if(st != null && st.length() > 0)
			{
				sql.append(" AND o.ORDER_TIME >=?");
				java.util.Date _st = org.apache.commons.lang.time.DateUtils.parseDate(st, new String[]{"yyyy-MM-dd"});
				ll.add(_st);
			}
			if(et != null && et.length() > 0)
			{
				sql.append(" AND o.ORDER_TIME <?");
				java.util.Date _et = org.apache.commons.lang.time.DateUtils.parseDate(et, new String[]{"yyyy-MM-dd"});
				ll.add(_et);
			}
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (o.PHONE LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY o.OID DESC");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.vo.OrderVO> objs = QueryHelper.query(com.bingo.mpa.vo.OrderVO.class, sql.toString(),param);
			
			sql = new StringBuffer("SELECT * FROM P_PACKAGE");
			List<com.bingo.mpa.pojo.Package> lp = QueryHelper.query_cache(com.bingo.mpa.pojo.Package.class, "packages", "packages", sql.toString(), new Object[]{});
			Map<Integer,com.bingo.mpa.pojo.Package> map = new HashMap<Integer,com.bingo.mpa.pojo.Package>();
			if(lp != null && lp.size() > 0)
			{
				for(com.bingo.mpa.pojo.Package obj : lp)
				{
					map.put(Integer.valueOf(obj.getPid()), obj);
				}
			}
			NumberFormat currentFormat=NumberFormat.getCurrencyInstance(Locale.CHINA);
			com.bingo.mpa.pojo.Package tmp = null;
			
			wwb = Workbook.createWorkbook(response.getOutputStream());
			WritableSheet ws = wwb.createSheet("订单", 0);
			//列头
			ws.addCell(new Label(0, 0, "订单时间"));
			ws.addCell(new Label(1, 0, "门店"));
			ws.addCell(new Label(2, 0, "工号"));
			ws.addCell(new Label(3, 0, "手机号码"));
			ws.addCell(new Label(4, 0, "套餐月费"));
			ws.addCell(new Label(5, 0, "价格"));
			ws.addCell(new Label(6, 0, "预存"));
			ws.addCell(new Label(7, 0, "折扣"));
			ws.addCell(new Label(8, 0, "状态"));
			ws.addCell(new Label(9, 0, "支付"));
			
			int i = 1;
			for(com.bingo.mpa.vo.OrderVO obj :objs)
			{
				ws.addCell(new Label(0,i,org.apache.commons.lang.time.DateFormatUtils.format(obj.getOrder_time(), "yyyy-MM-dd HH:mm:ss")));
				ws.addCell(new Label(1,i,obj.getName()));
				ws.addCell(new Label(2,i,obj.getS_account()));
				ws.addCell(new Label(3,i,obj.getPhone()));
				
				tmp = map.get(obj.getPackage_());
				if(tmp != null)
				{
					ws.addCell(new Label(4,i,tmp.getName()));
				}
				else
				{
					ws.addCell(new Label(4,i,""));
				}
				ws.addCell(new Label(5,i,currentFormat.format(obj.getRetail()/100.0)));
				ws.addCell(new Label(6,i,currentFormat.format(obj.getPrestore()/100.0)));
				ws.addCell(new Label(7,i,currentFormat.format(obj.getDiscount()/100.0)));
				ws.addCell(new Label(8,i,obj.getStatus()==1?"待开卡":obj.getStatus()==2?"已开卡":obj.getStatus()==3?"已撤单":obj.getStatus()==4?"已驳回":"未知"));
				ws.addCell(new Label(9,i,obj.getPay_status()==1?"未支付":obj.getPay_status()==2?"处理中":obj.getPay_status()==3?"已支付":obj.getPay_status()==4?"支付失败":"未知"));
				i++;
			}
			
			response.reset(); // 非常重要   
            response.setContentType("application/x-msdownload");   
            response.setHeader("Content-Disposition","attachment;filename=order.xls");   
			wwb.write();
			wwb.close();
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return null;
	}
	
	@Mapping("/log")
	public Renderer log()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			String token = HttpUtils.getCookie("mpa_token");
			String sql = "SELECT * FROM P_USER WHERE TOKEN=?";
			if(token == null)
			{
				return new RedirectRenderer("/login");
			}
			com.bingo.mpa.pojo.User user = QueryHelper.read_cache(com.bingo.mpa.pojo.User.class, "user", token, sql, new Object[]{token});
			if(user == null)
			{
				return new RedirectRenderer("/login");
			}
			byte role = user.getRole();
			switch(role)
			{
				case 2:
				case 3:
				case 4:
					return new RedirectRenderer("/login");
			}
			
			int type = HttpUtils.getIntParameter("type", -1);
			int severity = HttpUtils.getIntParameter("severity", -1);
			String dateType = HttpUtils.getStringParameter("dateType");
			String st = HttpUtils.getStringParameter("st");
			String et = HttpUtils.getStringParameter("et");
			if(dateType == null)
			{
				dateType = "today";
				st = Common.getMetaDate(dateType).getSt();
				et = Common.getMetaDate(dateType).getEt();
			}
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			StringBuffer sqll = new StringBuffer("SELECT COUNT(LID) FROM P_LOG WHERE 1=1");
			List<Object> ll = new ArrayList<Object>();
			if(type > 0)
			{
				sqll.append(" AND TYPE=?");
				ll.add(type);
			}
			if(severity > 0)
			{
				sqll.append(" AND SEVERITY=?");
				ll.add(severity);
			}
			if(st != null && st.length() > 0)
			{
				sqll.append(" AND LTIME >=?");
				java.util.Date _st = org.apache.commons.lang.time.DateUtils.parseDate(st, new String[]{"yyyy-MM-dd"});
				ll.add(_st);
			}
			if(et != null && et.length() > 0)
			{
				sqll.append(" AND LTIME <?");
				java.util.Date _et = org.apache.commons.lang.time.DateUtils.parseDate(et, new String[]{"yyyy-MM-dd"});
				ll.add(_et);
			}
			if(keyword != null && keyword.length()>0)
			{
				sqll.append(" AND (CLIENT_IP LIKE '%"+keyword+"%'");
				sqll.append(" OR LTEXT LIKE '%"+keyword+"%')");
			}
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			long count = QueryHelper.stat(sqll.toString(), param);
			model.put("type", type);
			model.put("severity", severity);
			model.put("dateType", dateType);
			model.put("st", st);
			model.put("et", et);
			model.put("keyword", keyword);
			model.put("count", count);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/log.html","model",model);
	}
	
	@Mapping("/log/page")
	public Renderer log_page()
	{
		StringBuilder sb = new StringBuilder();
		try
		{
			int page = HttpUtils.getIntParameter("page",1);
			int pageSize = HttpUtils.getIntParameter("pageSize", 9);
			
			int type = HttpUtils.getIntParameter("type", -1);
			int severity = HttpUtils.getIntParameter("severity", -1);
			String st = HttpUtils.getStringParameter("st");
			String et = HttpUtils.getStringParameter("et");
			String keyword = HttpUtils.getStringParameter("keyword");
			keyword = Tools.replaceStr(keyword);
			List<Object> ll = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("SELECT * FROM P_LOG WHERE 1=1");
			if(type > 0)
			{
				sql.append(" AND TYPE=?");
				ll.add(type);
			}
			if(severity > 0)
			{
				sql.append(" AND SEVERITY=?");
				ll.add(severity);
			}
			if(st != null && st.length() > 0)
			{
				sql.append(" AND LTIME >=?");
				java.util.Date _st = org.apache.commons.lang.time.DateUtils.parseDate(st, new String[]{"yyyy-MM-dd"});
				ll.add(_st);
			}
			if(et != null && et.length() > 0)
			{
				sql.append(" AND LTIME <?");
				java.util.Date _et = org.apache.commons.lang.time.DateUtils.parseDate(et, new String[]{"yyyy-MM-dd"});
				ll.add(_et);
			}
			if(keyword != null && keyword.length()>0)
			{
				sql.append(" AND (CLIENT_IP LIKE '%"+keyword+"%'");
				sql.append(" OR LTEXT LIKE '%"+keyword+"%')");
			}
			sql.append(" ORDER BY LID DESC");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			List<com.bingo.mpa.pojo.Log> logs = QueryHelper.query_slice(com.bingo.mpa.pojo.Log.class, sql.toString(), page, pageSize,param);
			
			for(com.bingo.mpa.pojo.Log log :logs)
			{
				sb.append("<tr><td>");
				sb.append(org.apache.commons.lang.time.DateFormatUtils.format(log.getLtime(), "yyyy-MM-dd HH:mm:ss"));
				sb.append("</td><td>");
				sb.append(log.getClient_ip());
				sb.append("</td><td>");
				sb.append(log.getType()==1?"操作日志":log.getType()==2?"运行日志":log.getType()==3?"用户日志":"安全日志");
				sb.append("</td><td>");
				sb.append(log.getSeverity()==1?"信息":log.getSeverity()==2?"警告":"错误");
				sb.append("</td><td>");
				sb.append(log.getLtext());
				sb.append("</td></tr>");
			}
			if(logs.size()<9)
			{
				for(int i = 0;i<9-logs.size();i++)
				{
					sb.append("<tr><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			log.info(page+","+pageSize);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(sb.toString());
	}
	
	@Mapping("/admin/report")
	public Renderer report()
	{
		Map<String,Object> model = new HashMap<String,Object>();
		try
		{
			long aid = HttpUtils.getLongParameter("aid", -1);
			String st = HttpUtils.getStringParameter("st");
			String et = HttpUtils.getStringParameter("et");
			String sql = "SELECT * FROM P_AGENT";
			List<com.bingo.mpa.pojo.Agent> list = QueryHelper.query_cache(com.bingo.mpa.pojo.Agent.class, "stat_agents", "stat_agents", sql, new Object[]{});
			model.put("list", list);
			model.put("aid", aid);
			model.put("st", st);
			model.put("et", et);
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TemplateRenderer("/report.html","model",model);
	}
	
	@Mapping("/admin/report/agent")
	public Renderer report_agent()
	{
		Pack pack = new Pack();
		StringBuilder sb = new StringBuilder();
		try
		{
			long aid = HttpUtils.getLongParameter("aid", -1);
			String st = HttpUtils.getStringParameter("st");
			String et = HttpUtils.getStringParameter("et");
			log.debug("aid="+aid+",st="+st+",et="+et);
			StringBuffer sql = new StringBuffer("SELECT a.NAME,o.AID as aid,o.CARRIER as carrier,COUNT(o.OID) as count,SUM(o.RETAIL)/100 as retail,SUM(o.DISCOUNT)/100 as discount FROM P_ORDER o,P_AGENT a");
			StringBuffer sto = new StringBuffer("SELECT s.NAME, o.SID as sid,o.CARRIER as carrier,COUNT(o.OID) as count,SUM(o.RETAIL)/100 as retail,SUM(o.DISCOUNT)/100 as discount FROM P_ORDER o,P_STORE s");
			sql.append(" WHERE o.AID=a.AID AND o.STATUS=2");
			sto.append(" WHERE o.SID=s.SID AND o.STATUS=2");
			List<Object> ll = new ArrayList<Object>();
			if(aid > 0)
			{
				sql.append(" AND o.AID=?");
				sto.append(" AND o.AID=?");
				ll.add(aid);
			}
			if(st != null && st.length() > 0)
			{
				sql.append(" AND ORDER_TIME >=?");
				sto.append(" AND ORDER_TIME >=?");
				java.util.Date _st = org.apache.commons.lang.time.DateUtils.parseDate(st, new String[]{"yyyy-MM-dd"});
				ll.add(_st);
			}
			if(et != null && et.length() > 0)
			{
				sql.append(" AND ORDER_TIME <?");
				sto.append(" AND ORDER_TIME <?");
				java.util.Date _et = org.apache.commons.lang.time.DateUtils.parseDate(et, new String[]{"yyyy-MM-dd"});
				ll.add(_et);
			}
			sql.append(" GROUP BY o.AID,o.CARRIER");
			sql.append(" ORDER BY count DESC");
			
			sto.append(" GROUP BY o.SID,o.CARRIER");
			sto.append(" ORDER BY count DESC");
			Object[] param = new Object[ll.size()];
			ll.toArray(param);
			
			//按代理商统计
			List<com.bingo.mpa.vo.StatByAgent> list = QueryHelper.query(com.bingo.mpa.vo.StatByAgent.class, sql.toString(), param);
			for(com.bingo.mpa.vo.StatByAgent obj :list)
			{
				sb.append("<tr><td width=\"90\">");
				sb.append(obj.getName());
				sb.append("</td><td width=\"60\">");
				sb.append(obj.getCarrier()==1?"中国移动":obj.getCarrier()==2?"中国联通":obj.getCarrier()==3?"中国电信":"");
				sb.append("</td><td width=\"90\">");
				sb.append(obj.getRetail());
				sb.append("</td><td width=\"60\">");
				sb.append(obj.getDiscount());
				sb.append("</td><td width=\"150\">");
				sb.append(obj.getCount());
				sb.append("</td></tr>");
			}
			if(list.size()<9)
			{
				for(int i = 0;i<9-list.size();i++)
				{
					sb.append("<tr><td width=\"90\">");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			pack.setA(sb.toString());
			
			//按门店统计
			sb.setLength(0);
			List<com.bingo.mpa.vo.StatByStore> s_list = QueryHelper.query(com.bingo.mpa.vo.StatByStore.class, sto.toString(), param);
			for(com.bingo.mpa.vo.StatByStore obj :s_list)
			{
				sb.append("<tr><td width=\"90\">");
				sb.append(obj.getName());
				sb.append("</td><td width=\"60\">");
				sb.append(obj.getCarrier()==1?"中国移动":obj.getCarrier()==2?"中国联通":obj.getCarrier()==3?"中国电信":"");
				sb.append("</td><td width=\"90\">");
				sb.append(obj.getRetail());
				sb.append("</td><td width=\"60\">");
				sb.append(obj.getDiscount());
				sb.append("</td><td width=\"150\">");
				sb.append(obj.getCount());
				sb.append("</td></tr>");
			}
			if(list.size()<9)
			{
				for(int i = 0;i<9-list.size();i++)
				{
					sb.append("<tr><td width=\"90\">");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td><td>");
					sb.append("</td></tr>");
				}
			}
			pack.setB(sb.toString());
		}
		catch(Exception e)
		{
			log.error("",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(pack),"utf-8");
	}
	
	@Mapping("/admin/notify")
	public Renderer notify_()
	{
		long count = 0L;
		try
		{
			String sql = "SELECT COUNT(OID) FROM P_ORDER WHERE STATUS=1";
			count = QueryHelper.stat(sql, new Object[]{});
		}
		catch(Exception e)
		{
			log.error("消息通知",e);
		}
		return new TextRenderer(com.alibaba.fastjson.JSON.toJSONString(count),"utf-8");
	}
	
	@Mapping("/s")
	public Renderer test()
	{
		return new TemplateRenderer("/sound.html");
	}
}
